#ifndef SQLHELPER_H
#define SQLHELPER_H

#include <QObject>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include <QFile>

class SqlHelper : public QObject
{
    Q_OBJECT
public:
    static SqlHelper& getInstance();
    std::optional<QVariantList> AuthLogin(const QString&, const QString&);

    bool insertUser(const QString&, const QString&, const QString&);
    QList<QVariantList> queryUsers();
    bool updateUser(const QString&, const QString&, const QString&, const QString&);
    bool deleteUser(const QString&);

    bool insertStudent(const QString&, const QString&, const QString&);
    bool insertCheckIn(const QString& studentName);
    QList<QVariantList> queryCheckIn(const QString& studentName = "");
    bool updateCheckin(int id, const QString& studentName, const QString& studentId, const QString& checkinDate, const QString& checkinTime, const QString& status, const QString& remarks);
    bool deleteCheckin(int id);

    bool insertDormitory(const QString &dormitoryNumber, const QString &dormitoryName, int floor, int totalPeople);
    QList<QVariantList> queryDormitories();
    bool updateDormitory(int id, const QString &dormitoryNumber, const QString &dormitoryName, int floor, int totalPeople);
    bool deleteDormitory(int id);

    bool insertJob(const QString &employeeName, const QString &position, int dormitoryId);
    QList<QVariantList> queryJobs();
    bool updateJob(int id, const QString &employeeName, const QString &position, int dormitoryId);
    bool deleteJob(int id);

    bool insertPowerUsage(int dormitoryId, double powerUsage);
    QList<QVariantList> queryPowerUsage();
    bool updatePowerUsage(int id, int dormitoryId, double powerUsage);
    bool deletePowerUsage(int id);

public:
    bool openDatabase();
    void closeDatabase();
    void deleteDataBase();
    void createTables();
    void fillInPreliminaryData();

private:
    QSqlQuery executeQuery(const QString &queryStr);

private:
    SqlHelper(QObject *parent = nullptr);
    ~SqlHelper();

    SqlHelper(const SqlHelper&) = delete;
    SqlHelper& operator=(const SqlHelper&) = delete;

private:
    QSqlDatabase db;
};

#endif
